package daoImpls;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import daoFactory.DaoFactory;
import vo.Good;
import vo.PetObject;

public class GoodDAOImpl<E> extends ObjectDAOImpl {

    @Override
    public void add(PetObject petObject) {
        Good newGood =(Good)petObject;
        Connection c = cp.getConnection();
        String sql = "insert into good values(null,?,?,?,?,?,?)";
        try (PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1,newGood.getgName());
            ps.setInt(2,Integer.parseInt(newGood.getCatID()));
            ps.setString(3,newGood.getgMsg());
            ps.setString(4,newGood.getGsrc());
            ps.setDouble(5,newGood.getgPrice());
            ps.setInt(6, newGood.getGnum());
            ps.execute();
            cp.returnConnection(c);
        } catch (SQLException e) {
        	System.out.println(e.getMessage());
        }
    }

    @Override
    public void update(PetObject petObject) {
        Good good = (Good)petObject;
        Connection c = cp.getConnection();
    	String sql = "update good set gname=?,gmsg=?,gprice=?,gnum=?,CatID=?,Gsrc=? where gid="+ good.getGid();
		try(PreparedStatement p = c.prepareStatement(sql)){
    		p.setString(1, good.getgName());
    		p.setString(2, good.getgMsg());
    		p.setDouble(3,good.getgPrice());
    		p.setInt(4, good.getGnum());
    		p.setString(5, good.getCatID());
    		p.setString(6, good.getGsrc());
    		p.execute();
    		p.close();
    		cp.returnConnection(c);
    	} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
    	
    }

    @Override
    public Good get(String id) {
        Good good = null;
        Connection c = cp.getConnection();
        try (Statement s = c.createStatement();) {
            String sql = "select * from good where GID = " + id;
            ResultSet rs = s.executeQuery(sql);
           
            if (rs.next()) {
                good = new Good();
                good.setGid(rs.getString(1));
                good.setgName(rs.getString(2));
                good.setCatID(rs.getString(3));
                good.setgMsg(rs.getString(4));
                good.setGsrc(rs.getString(5));
                good.setgPrice(rs.getDouble(6));
                good.setGnum(rs.getInt(7));
            }
            rs.close();
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return good;
    }

    public void delete(String id) {
        super.delete(id,"gid");
    }
    
   public void deleteQuery(String ids){
	   super.deleteQuery(ids, "gid");
   }

	@Override
    public List<PetObject> list() {
    	List<PetObject> listGood = new ArrayList<>();
    	Connection c = cp.getConnection();
    	PreparedStatement ps;
    	ResultSet rs;
			try {
				String sql = "select * from good order by gid desc";
				ps = c.prepareStatement(sql);
				rs = ps.executeQuery();
				while(rs.next()){
					Good good = new Good();
					good.setGid(rs.getString("gid"));
					good.setgName(rs.getString("GName"));
					good.setCatID(rs.getString("CatID"));
					good.setgMsg(rs.getString("GMsg"));
					good.setGsrc(rs.getString("Gsrc"));
					good.setgPrice(rs.getDouble("GPrice"));
					good.setGnum(rs.getInt("Gnum"));
					listGood.add(good);
				}
				rs.close();
		        cp.returnConnection(c);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        return listGood;
    }

    @Override
    public List<PetObject> list(int start, int count) {
    	List<PetObject> listGood = new ArrayList<>();
    	Connection c = cp.getConnection();
    	PreparedStatement ps;
    	ResultSet rs;
			try {
				String sql = "select * from good order by gid desc limit ?,?";
				ps = c.prepareStatement(sql);
				ps.setInt(1, start);
				ps.setInt(2, count);
				rs = ps.executeQuery();
				
				while(rs.next()){
					Good good = new Good();
					good.setGid(rs.getString("gid"));
					good.setgName(rs.getString("GName"));
					good.setCatID(rs.getString("CatID"));
					good.setgMsg(rs.getString("GMsg"));
					good.setGsrc(rs.getString("Gsrc"));
					good.setgPrice(rs.getDouble("GPrice"));
					good.setGnum(rs.getInt("Gnum"));
					listGood.add(good);
				}
				
				rs.close();
				cp.returnConnection(c);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        return listGood;
    }
    public List<PetObject> list(String s) {
    	List<PetObject> listGood = new ArrayList<>();
    	Connection c = cp.getConnection();
    	PreparedStatement ps;
    	ResultSet rs;
			try {
				String sql = "select * from good where gname like '%"+ s +"%' ORDER BY gid";
				ps = c.prepareStatement(sql);
				rs = ps.executeQuery();
				while(rs.next()){
					Good good = new Good();
					good.setGid(rs.getString("gid"));
					good.setgName(rs.getString("GName"));
					good.setCatID(rs.getString("CatID"));
					good.setgMsg(rs.getString("GMsg"));
					good.setGsrc(rs.getString("Gsrc"));
					good.setgPrice(rs.getDouble("GPrice"));
					good.setGnum(rs.getInt("Gnum"));
					listGood.add(good);
				}
				
				rs.close();
				cp.returnConnection(c);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        return listGood;
    }

}
